<?php

require_once dirname(dirname(__FILE__)) . '/config/config.php';
require_once DOCUMENT_ROOT . DIR_SHARED_CONFIG . 'db_connection.php';
require_once DOCUMENT_ROOT . DIR_SHARED_LIBRARIES . 'lib_date.php';

class dao_working_paper {

    //Methods
    public function __construct() {
        
    }

    public function get_all() {
        $db = new db_connection();
        $con = $db->open_connect();

        $query = "SELECT * FROM tbl_working_papers";
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }

        $db->close_connect($con);
        return $list;
    }

    public function get_default_description() {
        $db = new db_connection();
        $con = $db->open_connect();
        
        $query = "SELECT * FROM tbloption WHERE id = 1";
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());
        $row = mysqli_fetch_array($result);
        
        $db->close_connect($con);
        return $row;
    }
    
    public function set_default_description($default_description) {
        $db = new db_connection();
        $con = $db->open_connect();
        
        $query = "UPDATE tbloption SET paper_default_description = '" . $default_description . "' WHERE id = 1";
        mysqli_query($con, $query) or die("Query fail: " . mysqli_error());
        
        $db->close_connect($con);
        return true;
    }
    
    public function add(
            $paper_id, 
            $title, 
            $author, 
            $citation, 
            $keywords, 
            $article_type, 
            $pages, 
            $doi, 
            $publisher, 
            $acknown, 
            $pdf, 
            $abstract, 
            $detail_article,
            $dated_by_user, 
            $cat_id) {
        $db = new db_connection();
        $con = $db->open_connect();

        $query = "CALL addWorkingPapers(" . $paper_id . ", '" . $title . "', '" . $author . "', '" . $citation . "', '" . $keywords . "', '" . $article_type . "', '" . $pages . "', '" . $doi . "', '" . $publisher . "', '" . $acknown . "', '" . $abstract . "', '" . $detail_article . "', '" . $pdf . "', '" . lib_date::get_now() . "', '" . $dated_by_user . "', '" . $cat_id . "')";
        mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $db->close_connect($con);
        return true;
    }

    public function get_by_id($paper_id) {
        $db = new db_connection();
        $con = $db->open_connect();

        $query = "CALL GetWorkingPapersById(" . $paper_id . ")";
        $result = mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $row = mysqli_fetch_array($result);

        $db->close_connect($con);
        return $row;
    }

    public function delete($paper_id) {
        $db = new db_connection();
        $con = $db->open_connect();

        $query = "DELETE FROM tbl_working_papers WHERE PaperID = " . $paper_id;
        mysqli_query($con, $query) or die("Query fail: " . mysqli_error());

        $db->close_connect($con);
        return true;
    }

    function get_all_archive_by_paper_category_id($paper_category_id) {
        $papers = $this->get_all_by_paper_category_id_with_datebyuser_desc_sort($paper_category_id);
        $archives = array();
        $count = 0;
        foreach ($papers as $i => $item) {
            if (!empty($item["DateByUser"])) {
                $date = date_create($item["DateByUser"]);
                $month = date_format($date, 'm');
                $year = date_format($date, 'Y');
                $date_string = date_format($date, 'F Y');
                $archive = array();
                $archive['month'] = $month;
                $archive['year'] = $year;
                $archive['date_string'] = $date_string;
                $archive['cat_id'] = $item["cat_id"];
                if ($count == 0 || $archive['date_string'] != $archives[$count - 1]['date_string']) {
                    $archives[$count] = $archive;
                    $count = $count + 1;
                }
            }
        }
        return $archives;
    }
    
    function get_all_by_paper_category_id($paper_category_id) {
        $db = new db_connection();
        $con = $db->open_connect();
        //run the store proc
        $result = mysqli_query($con, "CALL GetAllWorkingPapersByCategoryID(" . $paper_category_id . ")")
                or die("Query fail: " . mysqli_error());

        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }

        $db->close_connect($con);
        return $list;
    }
    
    function get_all_by_paper_category_id_with_datebyuser_desc_sort($paper_category_id) {
        $db = new db_connection();
        $con = $db->open_connect();
        //run the store proc
        $result = mysqli_query($con, "SELECT * FROM tbl_working_papers WHERE cat_id = " . $paper_category_id . " ORDER BY DateByUser DESC")
                or die("Query fail: " . mysqli_error());

        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }

        $db->close_connect($con);
        return $list;
    }
    
    public function get_all_by_paper_category_id_and_date($category_id, $month, $year) {
        $db = new db_connection();
        $con = $db->open_connect();
        //run the store proc
        $result = mysqli_query($con, "CALL GetAllWorkingPapersByCategoryIDnDated(" . $category_id . ",'" . $month . "','" . $year . "')")
                or die("Query fail: " . mysqli_error());

        $list = array();
        while ($row = mysqli_fetch_array($result)) {
            array_push($list, $row);
        }

        $db->close_connect($con);
        return $list;
    }
    
}
